/**
 * 副将表增加字段迁移脚本
 * 添加 roleType、star、lvl、exp 字段
 */

const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库配置 - 使用远程数据库
const DB_CONFIG = {
    host: process.env.DB_HOST || '43.143.253.188',
    port: parseInt(process.env.DB_PORT || '3306', 10),
    user: process.env.DB_USER || 'nextjs',
    password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
    database: process.env.DB_NAME || 'nextjs',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

class AddDeputyFieldsMigration {
    constructor() {
        this.connection = null;
    }

    async connect() {
        try {
            this.connection = await mysql.createConnection(DB_CONFIG);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    async disconnect() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    async addFields() {
        console.log('\n🔧 步骤1: 添加新字段到副将表...');

        const fields = [
            {
                name: 'roleType',
                definition: 'ADD COLUMN `roleType` int NOT NULL DEFAULT 1 COMMENT \'职业类型(1:武士,2:文人,3:异师)\' AFTER `trans_count`'
            },
            {
                name: 'star',
                definition: 'ADD COLUMN `star` int NOT NULL DEFAULT 0 COMMENT \'星级(0-5)\' AFTER `roleType`'
            },
            {
                name: 'lvl',
                definition: 'ADD COLUMN `lvl` int NOT NULL DEFAULT 1 COMMENT \'等级\' AFTER `star`'
            },
            {
                name: 'exp',
                definition: 'ADD COLUMN `exp` bigint NOT NULL DEFAULT 0 COMMENT \'升级还需要的经验值\' AFTER `lvl`'
            }
        ];

        for (const field of fields) {
            try {
                // 检查字段是否已存在
                const [columns] = await this.connection.execute(
                    `SHOW COLUMNS FROM deputy_general LIKE '${field.name}'`
                );

                if (columns.length > 0) {
                    console.log(`⚠️ 字段 ${field.name} 已存在，跳过添加`);
                    continue;
                }

                await this.connection.execute(
                    `ALTER TABLE deputy_general ${field.definition}`
                );
                console.log(`✅ 成功添加字段: ${field.name}`);
            } catch (error) {
                console.error(`❌ 添加字段 ${field.name} 失败:`, error.message);
                // 继续添加其他字段
            }
        }
    }

    async updateExistingData() {
        console.log('\n🔄 步骤2: 更新现有数据...');

        try {
            // 更新现有副将的默认值
            await this.connection.execute(`
                UPDATE deputy_general 
                SET 
                    roleType = 1,
                    star = 0,
                    lvl = 1,
                    exp = 0
                WHERE roleType IS NULL OR star IS NULL OR lvl IS NULL OR exp IS NULL
            `);

            const [result] = await this.connection.execute(
                'SELECT COUNT(*) as count FROM deputy_general'
            );

            console.log(`✅ 已更新 ${result[0].count} 条副将记录`);
        } catch (error) {
            console.error('❌ 更新现有数据失败:', error.message);
        }
    }

    async verifyMigration() {
        console.log('\n🔍 步骤3: 验证迁移结果...');

        try {
            const [structure] = await this.connection.execute("DESCRIBE deputy_general");

            console.log(`📋 副将表字段数: ${structure.length}`);

            // 检查新增的字段
            const newFields = ['roleType', 'star', 'lvl', 'exp'];
            const existingFields = structure.map(col => col.Field);

            console.log('\n📋 新增字段验证:');
            newFields.forEach(field => {
                if (existingFields.includes(field)) {
                    console.log(`  ✅ ${field}: 存在`);
                } else {
                    console.log(`  ❌ ${field}: 不存在`);
                }
            });

            // 显示表结构
            console.log('\n📋 完整表结构:');
            structure.forEach(field => {
                console.log(`  - ${field.Field}: ${field.Type} ${field.Null === 'YES' ? '(可空)' : '(非空)'} ${field.Default ? `默认值: ${field.Default}` : ''}`);
            });

            console.log('✅ 迁移验证完成');
        } catch (error) {
            console.error('❌ 验证失败:', error.message);
            throw error;
        }
    }

    async runMigration() {
        console.log('🚀 开始副将表字段添加迁移...\n');

        try {
            await this.connect();
            await this.addFields();
            await this.updateExistingData();
            await this.verifyMigration();

            console.log('\n🎉 副将表字段添加迁移完成！');
            console.log('\n📝 新增字段:');
            console.log('  - roleType: 职业类型(1:武士,2:文人,3:异师)');
            console.log('  - star: 星级(0-5)');
            console.log('  - lvl: 等级');
            console.log('  - exp: 升级还需要的经验值');

        } catch (error) {
            console.error('\n💥 迁移过程中发生错误:', error.message);
            throw error;
        } finally {
            await this.disconnect();
        }
    }
}

async function main() {
    const migration = new AddDeputyFieldsMigration();
    await migration.runMigration();
}

if (require.main === module) {
    main().catch((error) => {
        console.error('💥 迁移脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = AddDeputyFieldsMigration;
